package com.yang.dao;

import com.yang.pojo.Manager;
import com.yang.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 管理员数据查询的信息访问
 */
public class ManagerDao {
    public Manager selectManagerByLoginName(String loginName) throws SQLException {
        /**
         * 分三步骤
         *  1、定义sql指令
         *  2、连接数据源QueryRunner
         *  3、通过QueryRunner执行SQL
         */
        String sql = "select mgr_id mgrId,login_name loginName,login_pwd loginPwd,mgr_name mgrName,mgr_gender mgrGender,mgr_tel mgrTel,mgr_email mgrEmail,mgr_qq mgrQq,create_time createTime from tb_managers where login_name = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //BeanHandler<Manager>表示返回一个对象的结果，这里返回的是manager
        Manager manager = queryRunner.query(sql, new BeanHandler<Manager>(Manager.class), loginName);

        return manager;
    }

    //查询管理员信息
    public ArrayList<Manager> selectManager() throws SQLException {
        ArrayList<Manager> managerList = new ArrayList<>();
        String sql = "select mgr_id mgrId,login_name loginName,login_pwd loginPwd,mgr_name mgrName,mgr_gender mgrGender,mgr_tel mgrTel,mgr_email mgrEmail,mgr_qq mgrQq,create_time createTime from tb_managers";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        managerList = (ArrayList<Manager>) queryRunner.query(sql, new BeanListHandler<Manager>(Manager.class));
        return managerList;
    }

    //根据roleId查询mgrId的String类型的集合
    public List<String> selectMgrIdByRoleId(int roleId) throws SQLException {
        List<String> mgrIdList = new ArrayList<>();
        String sql = "SELECT mgr_id mgrId FROM tb_mgr_role WHERE role_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //自定义结果集处理器
        ResultSetHandler<List<String>> resultSetHandler = new ResultSetHandler<List<String>>(){
            @Override
            public List<String> handle(ResultSet resultSet) throws SQLException{
                List<String> list = new ArrayList<>();
                while (resultSet.next()){
                    String mid = resultSet.getString("mgrId");
                    list.add(mid);
                }
                return list;
            }
        };


        mgrIdList = queryRunner.query(sql, resultSetHandler, roleId);
        return mgrIdList;
    }
    //根据mgrId查询manager
    public Manager selectManagerByMgrId(String mgrId) throws SQLException {
        String sql = "select mgr_id mgrId,login_name loginName,login_pwd loginPwd,mgr_name mgrName,mgr_gender mgrGender,mgr_tel mgrTel,mgr_email mgrEmail,mgr_qq mgrQq,create_time createTime from tb_managers where mgr_id = ?";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        Manager manager = queryRunner.query(sql,new BeanHandler<Manager>(Manager.class),mgrId);
        return manager;
    }

    //保存管理员信息
//    public int insertManager(String mgrId, String loginName, String loginPwd, String mgrName, String mgrGender, String mgrTel, String mgrEmail, String mgrQq, Timestamp createTime) throws SQLException {
//        int i = 0;
//        String sql = "INSERT INTO tb_managers(mgr_id,login_name,login_pwd,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time) VALUES (?,?,?,?,?,?,?,?,?) ";
//        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
//        i = queryRunner.update(sql,mgrId,loginName,loginPwd,mgrName,mgrGender,mgrTel,mgrEmail,mgrQq,createTime);
//
//        return i;
//    }

    public int insertManager(Manager manager){
        int i = 0;
        try {
            String sql = "insert into tb_managers(mgr_id,login_name,login_pwd,mgr_name,mgr_gender,mgr_tel,mgr_email,mgr_qq,create_time) values(?,?,?,?,?,?,?,?,?)";
            Object[] params = {manager.getMgrId(),manager.getLoginName(),manager.getLoginPwd(),manager.getMgrName(),manager.getMgrGender(),manager.getMgrTel(),manager.getMgrEmail(),manager.getMgrQq(),manager.getCreateTime()};
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            i = queryRunner.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    //保存管理员与角色的关联
    public int insertManagerAndRole(String mgrId,int roleId) throws SQLException {
        int i = 0;
        String sql = "INSERT INTO tb_mgr_role(mgr_id,role_id) VALUES(?,?)";
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        i = queryRunner.update(sql,mgrId,roleId);

        return i;
    }
}